﻿using ProTrack.Entity;
using ProTrack.IRepository;
using ProTrack.IServices.Stock;
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;

namespace ProTrack.Services.Stock
{
    /// <summary>
    /// 库存查询实现接口
    /// </summary>
    public class ItemStockServices : IItemStockServices
    {
        public ItemStockServices(IBaseRepository db)
        {

            Db = db;
        }
        public IBaseRepository Db { get; }


        //分页获取
        public async Task<IEnumerable<ItemStock>> GetAsync(int PageIndex, int PageSize, string WarehouseId, string ItemTypeId, string ItemName)
        {
            string sql = @"select s.ItemId,s.WarehouseId,w.WarehouseName,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,i.ItemTypeName,i.PurchasePrice,i.SalePrice,s.StockCount,s.SalesCount,(s.StockCount-s.SalesCount) as CurrCount from ItemStock s 
            inner join Item i on s.ItemId=i.Id
            inner join Warehouse w on s.WarehouseId=w.Id where 1=1 ";

            if (!string.IsNullOrEmpty(WarehouseId))
            {
                sql += " and WarehouseId = @WarehouseId ";
            }

            if (!string.IsNullOrEmpty(ItemTypeId))
            {
                sql += " and ItemTypeId = @ItemTypeId ";
            }

            if (!string.IsNullOrEmpty(ItemName))
            {
                sql += " and ItemName like @ItemName ";
            }



            sql += " order by w.WarehouseName desc offset @PageSize*(@PageIndex-1) row fetch next @PageSize rows only ";

            var list = await Db.QueryAsync<ItemStock>(sql, new { PageIndex, PageSize, WarehouseId = $"{WarehouseId}", ItemTypeId = $"{ItemTypeId}", ItemName = $"%{ItemName}%" });
            return list;
        }

        //获取总条数
        public async Task<int> GetCountAsync(string WarehouseId, string ItemTypeId, string ItemName)
        {
            string sql = @"select count(*) from ItemStock s 
            inner join Item i on s.ItemId=i.Id
            inner join Warehouse w on s.WarehouseId=w.Id where 1=1 ";
            if (!string.IsNullOrEmpty(WarehouseId))
            {
                sql += " and WarehouseId = @WarehouseId ";
            }

            if (!string.IsNullOrEmpty(ItemTypeId))
            {
                sql += " and ItemTypeId = @ItemTypeId ";
            }

            if (!string.IsNullOrEmpty(ItemName))
            {
                sql += " and ItemName like @ItemName ";
            }



            var count = await Db.GetRecoreCountAsync(sql, new { WarehouseId = $"{WarehouseId}", ItemTypeId = $"{ItemTypeId}", ItemName = $"%{ItemName}%" });
            return count;
        }


        //分页获取明细

        public async Task<IEnumerable<ItemStockItem>> GetDetailsAsync(string WarehouseId, string ItemId, string OrderTypeName)
        {
            string sql = "";

            if (OrderTypeName!="全部")
            {
                switch (OrderTypeName)
                {
                    case "销售出库":
                        sql = @" select oi.Id,('销售出库') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,oi.Price,oi.Count,oi.Amount from OrderSalesExWarehouse o
                inner join OrderSalesExWarehouseItem oi on o.Id=oi.OrderSalesExWarehouseId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId";
                        break;
                    case "销售退货":
                        sql = @" select oi.Id,('销售退货') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,oi.Price,oi.Count,oi.Amount from OrderReturn o
                inner join OrderReturnItem oi on o.Id=oi.OrderReturnId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId";
                        break;
                    case "采购入库":
                        sql = @" select oi.Id,('采购入库') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,oi.Price,oi.Count,oi.Amount from WhPoInbound o
                inner join WhPoInboundItem oi on o.Id=oi.WhPoInboundId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId";
                        break;
                    case "采购退货":
                        sql = @"select oi.Id,('采购退货') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,oi.Price,oi.Count,oi.Amount from OrderPoReturn o
                inner join OrderPoReturnItem oi on o.Id=oi.OrderPoReturnId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId";
                        break;
                    case "其他入库":
                        sql = @"select oi.Id,('其他入库') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,oi.Price,oi.Count,oi.Amount from OtherWarehouse o
                inner join OtherWarehouseItem oi on o.Id=oi.OtherWarehouseId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId";
                        break;
                    case "其他出库":
                        sql = @" select oi.Id,('其他出库') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,oi.Price,oi.Count,oi.Amount from OtherExWarehouse o
                inner join OtherExWarehouseItem oi on o.Id=oi.OtherExWarehouseId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId";
                        break;
                    case "生产领料":
                        sql = @"select oi.Id,('生产领料') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,i.SalePrice,oi.Count ,(i.SalePrice*oi.Count) as Amount from OrderProductPick o
                inner join OrderProductPickItem oi on o.Id=oi.OrderProductPickId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId";
                        break;
                    case "生产退料":
                        sql = @"select oi.Id,('生产退料') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,i.SalePrice,oi.Count ,(i.SalePrice*oi.Count) as Amount from OrderProductReceive o
                inner join OrderProductReceiveItem oi on o.Id=oi.OrderProductReceiveId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId";
                        break;
                    case "仓库调拨-调入":
                        sql = @"select oi.Id,('仓库调拨-调入') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,i.SalePrice,oi.Count ,(i.SalePrice*oi.Count) as Amount from WarehouseAllocation o
                inner join WarehouseAllocationItem oi on o.Id=oi.WarehouseAllocationId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId";
                        break;
                    case "仓库调拨-调出":
                        sql = @"select oi.Id,('仓库调拨-调出') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,i.SalePrice,oi.Count ,(i.SalePrice*oi.Count) as Amount from WarehouseAllocation o
                inner join WarehouseAllocationItem oi on o.Id=oi.WarehouseAllocationId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.ExWarehouseId=@WarehouseId";
                        break;
                }
            }
            else
            {
                sql = @" select oi.Id,('销售出库') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,oi.Price,oi.Count,oi.Amount from OrderSalesExWarehouse o
                inner join OrderSalesExWarehouseItem oi on o.Id=oi.OrderSalesExWarehouseId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId
                union  
                select oi.Id,('销售退货') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,oi.Price,oi.Count,oi.Amount from OrderReturn o
                inner join OrderReturnItem oi on o.Id=oi.OrderReturnId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId
                union 
                select oi.Id,('采购入库') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,oi.Price,oi.Count,oi.Amount from WhPoInbound o
                inner join WhPoInboundItem oi on o.Id=oi.WhPoInboundId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId
                union  
                select oi.Id,('采购退货') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,oi.Price,oi.Count,oi.Amount from OrderPoReturn o
                inner join OrderPoReturnItem oi on o.Id=oi.OrderPoReturnId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId
                union 
                select oi.Id,('其他入库') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,oi.Price,oi.Count,oi.Amount from OtherWarehouse o
                inner join OtherWarehouseItem oi on o.Id=oi.OtherWarehouseId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId
                union  
                select oi.Id,('其他出库') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,oi.Price,oi.Count,oi.Amount from OtherExWarehouse o
                inner join OtherExWarehouseItem oi on o.Id=oi.OtherExWarehouseId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId
                union
                select oi.Id,('生产领料') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,i.SalePrice,oi.Count ,(i.SalePrice*oi.Count) as Amount from OrderProductPick o
                inner join OrderProductPickItem oi on o.Id=oi.OrderProductPickId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId
                union  
                select oi.Id,('生产退料') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,i.SalePrice,oi.Count ,(i.SalePrice*oi.Count) as Amount from OrderProductReceive o
                inner join OrderProductReceiveItem oi on o.Id=oi.OrderProductReceiveId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId
                union 
                select oi.Id,('仓库调拨-调入') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,i.SalePrice,oi.Count ,(i.SalePrice*oi.Count) as Amount from WarehouseAllocation o
                inner join WarehouseAllocationItem oi on o.Id=oi.WarehouseAllocationId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.WarehouseId=@WarehouseId
                union 
                select oi.Id,('仓库调拨-调出') as OrderType,i.ItemCode,i.ItemName,i.Spec,i.UnitOfMeasure,i.SalePrice,oi.Count ,(i.SalePrice*oi.Count) as Amount from WarehouseAllocation o
                inner join WarehouseAllocationItem oi on o.Id=oi.WarehouseAllocationId inner join Item i on oi.ItemId=i.Id where i.Id=@ItemId and o.ExWarehouseId=@WarehouseId
                ORDER BY OrderType";
            }


            var list = await Db.QueryAsync<ItemStockItem>(sql, new { WarehouseId = $"{WarehouseId}", OrderTypeName = $"{OrderTypeName}", ItemId = $"{ItemId}" });
            return list;
        }

    }
}
